﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace TVP
{
    class Program
    {
        static void Main(string[] args)
        {

            ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["SQL2008"];
            string cnStr = settings.ConnectionString;
            using (SqlConnection connection = new SqlConnection())
            {
                connection.ConnectionString = cnStr;
                DataTable dt = GetDataTable();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                cmd.CommandText = "NewEmployee";
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                SqlParameter tvpParam = cmd.Parameters.AddWithValue("@EmployeeDetails", dt);
                tvpParam.SqlDbType = SqlDbType.Structured;
                tvpParam.TypeName = "dbo.EmployeeTableType";
                connection.Open();
                cmd.ExecuteNonQuery();
            }
        }

        private static DataTable GetDataTable()
        {
            DataTable dt = new DataTable("emp");

            dt.Columns.Add("EmpID", System.Type.GetType("System.Int32"));
            dt.Columns.Add("EmpName", System.Type.GetType("System.String"));
            dt.Columns.Add("EmpEmail", System.Type.GetType("System.String"));


            dt.Rows.Add(CreateRow(dt, 1,"Brad Pitt","BradPitt@Oceans13.com"));
            dt.Rows.Add(CreateRow(dt, 2, "Matt Damon", "MattDamon@Oceans13.com"));
            dt.Rows.Add(CreateRow(dt, 3, "George Clooney", "GeorgeClooney@Oceans13.com"));
            return dt;
        }

        private static DataRow CreateRow(DataTable dt, int p, string p_3, string p_4)
        {
            DataRow row = dt.NewRow();
            row["EmpID"] = p;
            row["EmpName"] = p_3;
            row["EmpEmail"] = p_4;
            return row;
        }
    }
}
